 -- EXEC SP_WAFER_PKG_MEMS_STANDARD_COST_REPORT 2010, 3
 
 ALTER PROC SP_WAFER_PKG_MEMS_STANDARD_COST_REPORT ( @YEAR INT, @QUARTER INT)
 AS
 BEGIN
 	DECLARE @FAB_CURSOR CURSOR 
 	DECLARE @DIEBANK_ProcessStep_ID INT
 	DECLARE @WaferCost	NUMERIC(18,4)
 	DECLARE @BGCost		NUMERIC(18,4)
 	DECLARE @RawWaferCost	NUMERIC(18,4)
 	DECLARE @ZroExp2Cost	NUMERIC(18,4)
 	DECLARE @EpiVtCost	NUMERIC(18,4)
 	DECLARE @VnExpCost	NUMERIC(18,4)
 	DECLARE @EpiSealCost	NUMERIC(18,4)
 	DECLARE @PreGrindCost	NUMERIC(18,4)
 	DECLARE @SortCost	NUMERIC(18,4)
 	DECLARE @BGYield	NUMERIC(18,4)
 	DECLARE @YieldTest	NUMERIC(18,4)
 	DECLARE @SortYield		NUMERIC(18,4)
 	DECLARE @MixMatchYield		NUMERIC(18,4)
 	DECLARE @BackGrind_Out_Cost	NUMERIC(18,4)
 	DECLARE @Sort_Out_Cost	NUMERIC(18,4)
 	DECLARE @DieBank_Wafer_Cost	NUMERIC(18,4)
 	DECLARE @Cumm_Cost_Per_Die	NUMERIC(18,4)
 	DECLARE @GDW INT
 
 	CREATE TABLE #COST_TABLE ( [Part Number] VARCHAR(50), [GDW] INT, [Raw Wafer Cost] NUMERIC(18,4),
 			[ZRO EXP2 Cost] NUMERIC(18,4), [Zero EXP2 CUMM COST] NUMERIC(18,4), [Epi VT Cost] NUMERIC(18, 2), [Epi VT CUMM Cost] NUMERIC (18, 4), 
 			[VN_EXP Cost] NUMERIC(18,4), [VN_EXP CUMM COST] NUMERIC(18,4), [Epi Seal Cost] NUMERIC(18, 2), [Epi Seal CUMM Cost] NUMERIC (18, 4), 
 			[Fab Cost] NUMERIC(18,4), [Fab CUMM COST] NUMERIC(18,4), [Pre_Grind Cost] NUMERIC(18, 2), [Pre_Grind CUMM Cost] NUMERIC (18, 4), 
 			[Back Grind Step Cost] NUMERIC(18,4), [Back Grind Yield] NUMERIC(18,4), [Back Grind CUMM Cost] NUMERIC (18, 4), 
 			[Sort Step Cost] NUMERIC(18,4), [Sort Yield] NUMERIC(18,4), [Sort CUMM Cost] NUMERIC (18, 4), 
 			[Mix Match Yield] NUMERIC(18,4), [Total Wafer Cost] NUMERIC (18, 4), [Die Bank Die Cost] NUMERIC (18, 4), ProcessStep_Id INT )
 
 	INSERT INTO #COST_TABLE( [Part Number], ProcessStep_Id ) 
 	SELECT P.Part_Number, P.ProcessStep_ID FROM Product P, Process_Step PS WHERE P.ProcessStep_ID = PS.ProcessStep_ID AND PS.PS_Type = 'Die Bank' AND P.ProcessStep_ID IN ( SELECT ProcessStep_ID FROM Process_Parameter WHERE Parameter_key = 'IS_MEMS_WAFER' AND Parameter_Value = 'TRUE'  )
 
 	SET @FAB_CURSOR = CURSOR FOR SELECT ProcessStep_Id FROM #COST_TABLE
 	OPEN @FAB_CURSOR
 	FETCH NEXT FROM @FAB_CURSOR INTO @DIEBANK_ProcessStep_ID
 	WHILE @@FETCH_STATUS = 0
 	BEGIN
 			SELECT @WaferCost = [Wafer Cost], @BGCost = [BackGrind Cost],@RawWaferCost = [Raw_Wafer_Cost], @ZroExp2Cost = [ZRO_EXP2_Cost],
 					@EpiVtCost = [EPI_VT_Cost], @VnExpCost = [VN_EXP_Cost], @EpiSealCost = [Epi_Seal_Cost],@PreGrindCost = [Pre_Grind_Cost],
 					@BGYield = [Back_Grind_Yield],	@SortCost = [Sort_Cost],@SortYield = [Sort_Yield], @MixMatchYield = [Mix_Match_Yield]
 			  FROM [Wafer_Pkg_Cost_Table] WHERE ProcessStep_ID = @DIEBANK_ProcessStep_ID AND Quarter = @Quarter AND Year = @Year

			SELECT @GDW = Parameter_Value FROM Process_Parameter WHERE Parameter_key = 'FIN_GDW' AND ProcessStep_ID = ( SELECT TOP 1 ProcessStep_ID FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @DIEBANK_ProcessStep_ID, 0 ) WHERE PS_TYPE = 'FAB' )
			IF( @GDW IS NULL OR @GDW < 1  )
			BEGIN
				SELECT @GDW = Parameter_Value FROM Process_Parameter WHERE Parameter_key = 'Gross Die/Wafer' AND ProcessStep_ID = ( SELECT TOP 1 ProcessStep_ID FROM FN_GET_PROCESS_FLOWS_BY_ProcessStep_ID ( @DIEBANK_ProcessStep_ID, 0 ) WHERE PS_TYPE = 'FAB' )
			END			
 			
 			SET @BackGrind_Out_Cost = ( ISNULL( @RawWaferCost, 0.0) + ISNULL( @ZroExp2Cost, 0.0) + ISNULL( @EpiVtCost, 0.0) + ISNULL( @VnExpCost, 0.0 ) + ISNULL( @EpiSealCost, 0.0) + ISNULL( @WaferCost, 0.0) + ISNULL( @PreGrindCost, 0.0) + ISNULL( @BGCost, 0.0) )/(ISNULL(@BGYield, 100.00)/100.00)
 			SET @Sort_Out_Cost = (ISNULL( @BackGrind_Out_Cost, 0) + ISNULL( @SortCost, 0) )/(ISNULL(@SortYield, 100.00)/100.00)
 			SET @DieBank_Wafer_Cost = (ISNULL( @Sort_Out_Cost, 0) )/(ISNULL(@MixMatchYield, 100.00)/100.00)
 
 			UPDATE #COST_TABLE SET [GDW] =@GDW, [Raw Wafer Cost] = ISNULL( @RawWaferCost, 0.0), [ZRO EXP2 Cost] = ISNULL( @ZroExp2Cost, 0.0), [Zero EXP2 CUMM COST] = (ISNULL( @RawWaferCost, 0.0) + ISNULL( @ZroExp2Cost, 0.0)),
 			[Epi VT Cost] = @EpiVtCost, [Epi VT CUMM Cost] = (ISNULL( @RawWaferCost, 0.0) + ISNULL( @ZroExp2Cost, 0.0) + ISNULL( @EpiVtCost, 0.0) ), [VN_EXP Cost] = ISNULL( @VnExpCost, 0.0),
 			[VN_EXP CUMM COST] = (ISNULL( @RawWaferCost, 0.0) + ISNULL( @ZroExp2Cost, 0.0) + ISNULL( @EpiVtCost, 0.0) + ISNULL( @VnExpCost, 0.0)), [Epi Seal Cost] = ISNULL( @EpiSealCost, 0.0),
 			[Epi Seal CUMM Cost] = (ISNULL( @RawWaferCost, 0.0) + ISNULL( @ZroExp2Cost, 0.0) + ISNULL( @EpiVtCost, 0.0) + ISNULL( @VnExpCost, 0.0) + ISNULL( @EpiSealCost, 0.0) ),
 			[Fab Cost] = ISNULL( @WaferCost, 0.0), [Fab CUMM COST] = (ISNULL( @RawWaferCost, 0.0) + ISNULL( @ZroExp2Cost, 0.0) + ISNULL( @EpiVtCost, 0.0) + ISNULL( @VnExpCost, 0.0) + ISNULL( @EpiSealCost, 0.0) + ISNULL( @WaferCost, 0.0) ),
 			[Pre_Grind Cost] = ISNULL( @PreGrindCost, 0.0), [Pre_Grind CUMM Cost] = (ISNULL( @RawWaferCost, 0.0) + ISNULL( @ZroExp2Cost, 0.0) + ISNULL( @EpiVtCost, 0.0) + ISNULL( @VnExpCost, 0.0) + ISNULL( @EpiSealCost, 0.0) + ISNULL( @WaferCost, 0.0) + ISNULL( @PreGrindCost, 0.0) ),
 			[Back Grind Step Cost] = ISNULL( @BGCost, 0.0),  [Back Grind Yield] = ISNULL( @BGYield, 100.00),
 			[Back Grind CUMM Cost] = @BackGrind_Out_Cost, [Sort Step Cost] = @SortCost, [Sort Yield] = @SortYield, [Sort CUMM Cost] = @Sort_Out_Cost,
 			[Mix Match Yield] = @MixMatchYield, [Total Wafer Cost] = @DieBank_Wafer_Cost, [Die Bank Die Cost] = @DieBank_Wafer_Cost/ ISNULL(@GDW, 1)
 			WHERE ProcessStep_Id = @DIEBANK_ProcessStep_ID
 
			SET @WaferCost = 0.0
			SET @BGCost = 0.0
			SET @RawWaferCost = 0.0
			SET @ZroExp2Cost = 0.0
			SET @EpiVtCost = 0.0
			SET @VnExpCost = 0.0
			SET @EpiSealCost = 0.0
			SET @PreGrindCost = 0.0
			SET @BGYield = 0.0
			SET @SortCost = 0.0
			SET @SortYield = 0.0
			SET @MixMatchYield = 0.0
			SET @BackGrind_Out_Cost = 0.0
			SET @Sort_Out_Cost = 0.0
			SET @DieBank_Wafer_Cost = 0.0
			
 		FETCH NEXT FROM @FAB_CURSOR INTO @DIEBANK_ProcessStep_ID
 	END 
 	CLOSE @FAB_CURSOR
 	DEALLOCATE @FAB_CURSOR
 
 	SELECT [Part Number]  , [GDW],  [Raw Wafer Cost] ,[ZRO EXP2 Cost] , [Zero EXP2 CUMM COST] , [Epi VT Cost], [Epi VT CUMM Cost]  , [VN_EXP Cost] , [VN_EXP CUMM COST] , [Epi Seal Cost], [Epi Seal CUMM Cost]  , 
 			[Fab Cost] , [Fab CUMM COST] , [Pre_Grind Cost], [Pre_Grind CUMM Cost]  , [Back Grind Step Cost] , ([Back Grind Yield]/100.00) as [Back Grind Yield] , [Back Grind CUMM Cost]  , [Sort Step Cost] , ([Sort Yield]/100.00) as [Sort Yield] , [Sort CUMM Cost]  , 
 			([Mix Match Yield]/100.00) AS [Mix Match Yield] , [Total Wafer Cost]  , [Die Bank Die Cost]   FROM #COST_TABLE 
 
 END

